package com.itravel.cms.utils;

import java.io.IOException;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;


public class POIUtil {

	/**
	 * 创建一个excel Cell， 并将数据写入到这个Cell里
	 * @param row 要写入的excel行
	 * @param column 要写入的excel列
	 * @param value 具体要写入的数值
	 */
	public static Cell createCellAndSetValue(Row row, int column, Object value) {
		if (value == null) return null;
		Cell cell = row.getCell(column);
		if (cell == null)
			cell = row.createCell(column);
		cell.setCellValue(value.toString());
		//cell.getCellStyle().setAlignment(CellStyle.ALIGN_CENTER);
		return cell;
	}
	
	public static String getStringCellValue(Row row,int column) {
		Cell cell = row.getCell(column);
		return getStringCellValue(cell);
	}
	/**
	 * 获得单元格的字符串值
	 * @param cell
	 * @return String
	 */
	public static String getStringCellValue(Cell cell){
		if(null == cell) return "";
		String strCell = "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			strCell = cell.getStringCellValue().trim();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC: 
			DecimalFormat df = new DecimalFormat();
			df.applyPattern("#####.##");
			strCell = df.format(new Double(cell.getNumericCellValue()));
			//strCell = "" + cell.getNumericCellValue();
			break;  
		case HSSFCell.CELL_TYPE_BOOLEAN:  
			strCell = String.valueOf(cell.getBooleanCellValue());  
			break;  
		case HSSFCell.CELL_TYPE_BLANK:  
			strCell = "";  
			break;  
		default:  
			strCell = "";  
			break;
		}
		
		return strCell;
	}
	
	/**
	 * 根据路径获取模板
	 * @param path
	 * @return
	 * @throws IOException
	 */
	public static POIFSFileSystem getTemplate(String path) throws IOException {
       POIFSFileSystem fs = new POIFSFileSystem(POIUtil.class.getClassLoader().getResourceAsStream(path));
       return fs;
   }
	
	/**
	 * 根据sheet的标题行创建各个列的索引
	 * @param sheet 要创建标题索引的sheet
	 * @param titleRowNum 标题在sheet中的行号
	 * @return 一个包含了标题名和列号的map
	 * 
	 */
	public static Map<String,Integer> getColumnNameIndex(Sheet sheet,int titleRowNum) {
		Map<String,Integer> result = new HashMap<String,Integer> ();
		Row titleRow = sheet.getRow(titleRowNum);
		for (int i =titleRow.getFirstCellNum();i<titleRow.getLastCellNum();i++) {
			String title = getStringCellValue(titleRow.getCell(i));
			result.put(title.trim(), i);
		}
		return result;
	}
	
	public static Number getNumber(Row row, int column,ErrorHandler errorHandler) {
		String cellValue = POIUtil.getStringCellValue(row, column);
		if (StringUtils.isNotBlank(cellValue) ) {
			if (NumberUtils.isNumber(cellValue)) {
				return Double.valueOf(cellValue);
			} else {
				if (errorHandler != null) {
					errorHandler.handleError(row,column,cellValue);
				}
				return null;
			}
		}
		return null;
	}
	
	public static interface ErrorHandler {
		void handleError(Row row, int column,String value);
	}
}
